/**
 * @filename Db.java
 * @package top.h2v.xtrans.db[Tranformation]
 * @author 胡正衛 
 * @date 2012-12-1 上午10:11:06
 * @version v0.1
 */
package com.github.cdisk.xtrans.connect;

import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

/**
 * @author 胡正衛
 */
public class Db {

	private Connection connection;
	
	private Connect connect;


	public Db(Connect connect) {
		this.connect = connect;
	}

	public void open() throws SQLException {
		if (connection == null || connection.isClosed()) {
			String driver = connect.getDriver();
			Properties properties = new Properties();
			if (connect.getCharset() != null) properties.setProperty("charSet", connect.getCharset());
			if (connect.getUser() != null) properties.setProperty("user", connect.getUser());
			if (connect.getPassword() != null) properties.setProperty("password", connect.getPassword());
			if ("sun.jdbc.odbc.JdbcOdbcDriver".equals(driver)) { // ODBC初始化方式不同
				connection = DriverManager.getConnection(connect.getUrl(), connect.getUser(), connect.getPassword());
			} else {
				connection = DriverManager.getConnection(connect.getUrl(), properties.isEmpty() ? null : properties);
			}
			connection.setAutoCommit(true);
		}
	}
	
	public boolean isClosed() {
		try {
			return connection == null || connection.isClosed();
		} catch (Exception e) {
			close();
			return true;
		}
	}
	
	public boolean execute(String sql) throws SQLException {
		return execute(sql, null);
	}

	public boolean execute(String sql, Object[] param) throws SQLException {
		if (sql == null || sql.length() == 0) return false;
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement(sql);
			if (param != null && param.length > 0) {
				for (int i = 1; i <= param.length; i++) {
					stmt.setObject(i, param[i-1]);
				}
			}
			boolean result = stmt.execute();
			stmt.close();
			stmt = null;
			return result;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (stmt != null && !stmt.isClosed()) {stmt.close();}
		}
	}

	public int executeUpdate(String sql) throws SQLException {
		return executeUpdate(sql, null);
	}

	public int executeUpdate(String sql, Object[] param) throws SQLException {
		if (sql == null || sql.length() == 0) return -1;
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement(sql);
			if (param != null && param.length > 0) {
				for (int i = 0; i < param.length; i++) {
					Object value = param[i];
					if (value == null) {
						stmt.setNull(i + 1, java.sql.Types.NULL);
					}
					if (value instanceof Integer) {
						stmt.setInt(i + 1, (Integer) value);
					} else if (value instanceof String) {
						stmt.setString(i + 1, (String) value);
					} else if (value instanceof Timestamp) {
						stmt.setTimestamp(i + 1, (Timestamp) value);
					} else if (value instanceof Date) {
						stmt.setTimestamp(i + 1, new Timestamp(((Date) value).getTime()));
					} else if (value instanceof Double) {
						stmt.setDouble(i + 1, (Double) value);
					} else if (value instanceof Boolean) {
						stmt.setInt(i + 1, (Boolean) value ? 1 : 0);
					} else if (value instanceof BigDecimal) {
						stmt.setBigDecimal(i + 1, (BigDecimal) value);
					} else if (value instanceof Clob) {
						Clob oldClob = (Clob) value;
						Clob clob = connection.createClob();
						Writer writer = clob.setCharacterStream(0);
						Reader reader = oldClob.getCharacterStream();
						int read = 0;
						while ((read = reader.read()) > -1) {
							writer.write(read);
						}
						stmt.setClob(i + 1, clob);
					} else {
						stmt.setObject(i + 1, value);
					}
				}
			}
			int result = stmt.executeUpdate();
			stmt.close();
			stmt = null;
			return result;
		} catch (SQLException e) {
			throw e;
		} catch (IOException e) {
			throw new SQLException(e);
		} finally {
			if (stmt != null && !stmt.isClosed()) {stmt.close();}
		}
	}

//	public List<Map<String, Object>> executeQuery(String sql) throws SQLException {
//		return executeQuery(sql, null);
//	}
//
//	public List<Map<String, Object>> executeQuery(String sql, Object[] param) throws SQLException {
//		if (sql == null || sql.length() == 0) return null;
//		PreparedStatement stmt = null;
//		ResultSet rs = null;
//		try {
//			List<Map<String, Object>> result = new ArrayList<Map<String,Object>>();
//			stmt = connection.prepareStatement(sql);
//			if (param != null && param.length > 0) {
//				for (int i = 1; i <= param.length; i++) {
//					stmt.setObject(i, param[i-1]);
//				}
//			}
//			rs = stmt.executeQuery();
//			ResultSetMetaData rsmd = rs.getMetaData();
//			int col_len = rsmd.getColumnCount();
//			Map<String, Object> map;
//			while (rs.next()) {
//				map = new HashMap<String, Object>();
//				for (int i = 1; i <= col_len; i++) {
//					map.put(rsmd.getColumnName(i), rs.getObject(i));
//				}
//				result.add(map);
//			}
//			map = null;
//			rs.close();
//			rs = null;
//			stmt.close();
//			stmt = null;
//			return result;
//		} catch (SQLException e) {
//			throw e;
//		} finally {
//			if (rs != null && !rs.isClosed()) {rs.close();}
//			if (stmt != null && !stmt.isClosed()) {stmt.close();}
//		}
//	}



	public int executeQueryCount(String sql) throws SQLException {
		return executeQueryCount(sql, null);
	}

	public int executeQueryCount(String sql, Object[] param) throws SQLException {
		if (sql == null || sql.length() == 0) return -1;
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connection.prepareStatement("select count(*) from (" + sql + ") t");
			if (param != null && param.length > 0) {
				for (int i = 1; i <= param.length; i++) {
					stmt.setObject(i, param[i-1]);
				}
			}
			rs = stmt.executeQuery();
			rs.next();
			int count = rs.getInt(1);
			rs.close();
			stmt.close();
			return count;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (stmt != null && !stmt.isClosed()) {stmt.close();}
		}
	}

	public ResultSet executeQueryResult(String sql) throws SQLException {
		return executeQueryResult(sql, null);
	}

	public ResultSet executeQueryResult(String sql, Object[] param) throws SQLException {
		if (sql == null || sql.length() == 0) return null;
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connection.prepareStatement(sql);
			if (param != null && param.length > 0) {
				for (int i = 1; i <= param.length; i++) {
					stmt.setObject(i, param[i-1]);
				}
			}
			rs = stmt.executeQuery();
			return rs;
		} catch (SQLException e) {
			try {
				if (stmt != null && !stmt.isClosed()) {stmt.close();}
			} catch (Exception ex) { }
			throw e;
		} finally {
		}
	}

	public int insert(String table, Map<String, Object> map) throws SQLException {
		Set<String> keys = map.keySet();
		Iterator<String> it = keys.iterator();
		List<String> columns = new ArrayList<String>();
		while (it.hasNext()) {
			String key = it.next();
			if (map.get(key) == null) continue;
			columns.add(key);
		}

		String sql = "";
		List<Object> param = new ArrayList<Object>();
		sql += "insert into " + table + "(";
		for (int j = 0; j < columns.size(); j++) {
			sql += columns.get(j);
			if (j < columns.size() - 1)
				sql += ", ";
		}
		sql += ") values (";
		for (int j = 0; j < columns.size(); j++) {
			param.add(map.get(columns.get(j)));
			sql += "?";
			if (j < columns.size() - 1) {
				sql += ", ";
			}
		}
		sql += ")";
		return executeUpdate(sql, param.toArray());
	}

	public boolean executeCall(String sql) throws SQLException {
		return executeCall(sql, null);
	}

	public boolean executeCall(String sql, Object[] param) throws SQLException {
		if ((sql == null) || (sql.length() == 0)) return false;
		CallableStatement stmt = null;
		try {
			stmt = this.connection.prepareCall(sql);
			if ((param != null) && (param.length > 0)) {
				for (int i = 1; i <= param.length; i++) {
					stmt.setObject(i, param[(i - 1)]);
				}
			}
			stmt.execute();
			stmt.close();
			stmt = null;
			return true;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (stmt != null && !stmt.isClosed()) {stmt.close();}
		}
	}

	public void close() {
		try {
			if (connection != null & !connection.isClosed()) {
				connection.close();
				connection = null;
			}
		} catch (Exception e) {
		}
	}

}
